﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.OleDb;
using Microsoft.VisualBasic.FileIO;


public class ExcelReader
{
    public ExcelReader()
    {
    }

    public string Read(string ExcelPath, out DataSet dsResult)
    {
        string returnVal = string.Empty;
        //read Excel
        string ExcelConnString = string.Empty;
        ExcelConnString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + ExcelPath + "; Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1\";";
        //ExcelConnString = "Data Source=" + ExcelPath + "; Format=xls; HDR=Yes; IMEX=1\";";

        //if (ext == ".xls") xConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Server.MapPath(UploadDefaultPath + filename) + ";" + "Extended Properties=Excel 8.0;";
        //else if (ext == ".xlsx") xConnStr = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + Server.MapPath(UploadDefaultPath + filename) + ";" + "Extended Properties=Excel 8.0;";

        OleDbConnection objConn = new OleDbConnection(ExcelConnString);
        DataSet objDataset = null;
        string sSheetName = string.Empty;
        

        try
        {
            objConn.Open();
            DataTable dtTablesList = objConn.GetSchema("Tables");
            if (dtTablesList.Rows.Count > 0)
            {
                sSheetName = dtTablesList.Rows[0]["TABLE_NAME"].ToString();
            }

            dtTablesList.Clear();
            dtTablesList.Dispose();
            
            OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM ["+sSheetName+"]", objConn);
            OleDbDataAdapter objAdapter = new OleDbDataAdapter(objCmdSelect);
            objDataset = new DataSet();
            objAdapter.Fill(objDataset);
            returnVal = "Data has been successfully imported";
        }

        catch (Exception ex)
        {
            // Tools.LogWriter.LogWriter logwriter = new Tools.LogWriter.LogWriter();
            // logwriter.WriteLog(ex.Message.ToString(), ex.StackTrace.ToString());
            returnVal = "Error while importing data, please contact IT support";
        }

        finally
        {
            if (objConn != null)
            {
                objConn.Close();
                objConn.Dispose();
            }
        }

        //----------------------------
        dsResult = objDataset;
        return returnVal;
    }

    public static DataTable GetDataTabletFromCSVFile(string csv_file_path)
    {
        DataTable csvData = new DataTable();
        try
        {
          using(TextFieldParser csvReader = new TextFieldParser(csv_file_path))
             {
                csvReader.SetDelimiters(new string[] { "," });
                csvReader.HasFieldsEnclosedInQuotes = true;
                string[] colFields = csvReader.ReadFields();
                foreach (string column in colFields)
                {
                    DataColumn datecolumn = new DataColumn(column);
                    datecolumn.AllowDBNull = true;
                    csvData.Columns.Add(datecolumn);
                }
                while (!csvReader.EndOfData)
                {
                    string[] fieldData = csvReader.ReadFields();
                    //Making empty value as null
                    for (int i = 0; i < fieldData.Length; i++)
                    {
                        if (fieldData[i] == "")
                        {
                            fieldData[i] = null;
                        }
                    }
                    csvData.Rows.Add(fieldData);
                }
            }
        }
        catch (Exception ex)
        {
        }
        return csvData;
    }
  }

